package com.ruoyi.common.utils.poi;

import com.ruoyi.common.utils.file.FileUploadUtils;
import com.ruoyi.common.utils.file.FileUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.SQLOutput;
import java.util.ArrayList;;
import java.util.List;
import java.util.UUID;

public class ReadExcelUtil {

    public static String imagePath = "D:/image/";


    /**
     * 根据文件路径读取 Excel 文件中的数据
     * @param filePath excel文件路径
     * @return
     * @throws IOException
     */
    public static List<List<Object>> readExcel(String filePath) throws IOException {
        // 创建文件输入流
        FileInputStream fis = new FileInputStream(filePath);

        // 使用 XSSFWorkbook 读取 .xlsx 文件
        Workbook workbook = new XSSFWorkbook(fis);

        // 存储所有行数据
        List<List<Object>> allData = new ArrayList<>();

        // 存储图片路径
        List<Object> imageList = new ArrayList<>();

        // 遍历每个工作表
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            // 获取图片
            List<String> image = getImage(sheet);

            for (String imagePath :image) {
                File file = new File(imagePath);
                String contentType = getContentType(file.getName());
                MultipartFile multipartFile = new MockMultipartFile(file.getName(),file.getName(),contentType, new FileInputStream(file));
                // 添加图片路径
                String upload = FileUploadUtils.upload(multipartFile);
                String path = upload.replace("/profile//", "/profile/");
                imageList.add(path);
                FileUtils.deleteFile(imagePath);

            }

            // 遍历每一行
            for (int j = 5;j < sheet.getPhysicalNumberOfRows();j++) {
                Row row = sheet.getRow(j);
                // 存储每一行的数据
                List<Object> rowData = new ArrayList<>();

                // 遍历每一列
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
                            if (!cell.getStringCellValue().isEmpty()) {
                                rowData.add(cell.getStringCellValue());
                                break;
                            }
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                rowData.add(cell.getDateCellValue());
                            } else {
                                rowData.add(cell.getNumericCellValue());
                            }
                            break;
                        case BOOLEAN:
                            rowData.add(cell.getBooleanCellValue());
                            break;
                        default:
                            break;
                    }
                }

                // 添加当前行的数据到所有数据列表中
                allData.add(rowData);
            }
        }
        allData.add(imageList);
        // 关闭工作簿和文件输入流
        workbook.close();
        fis.close();
        // 移除 rowData 中的空元素，如 null 或空字符串 ""
        List<List<Object>> lists = removeEmptyArrays(allData);
        return lists;
    }


    /**
     * 获取Excel表中的图片
     * @param sheet
     */
    public static List<String> getImage(Sheet sheet){
        List<String> pathList = new ArrayList<>();

        // 确保目标路径存在
        File directory = new File(imagePath);
        if (!directory.exists()) {
            // 创建目录
            directory.mkdirs();
        }

        try {
            XSSFDrawing drawings = (XSSFDrawing) sheet.getDrawingPatriarch();
            if (drawings != null) {
                List<XSSFShape> shapes = drawings.getShapes();
                int i=0;
                int j = 0;
                for (XSSFShape shape : shapes) {
                    if (shape instanceof XSSFPicture) {
                        ++i;
                        if (i ==10){
                            continue;
                        }

                        XSSFPicture picture = (XSSFPicture) shape;
                        PictureData pictureData = picture.getPictureData();
                        byte[] data = pictureData.getData();

                        // 获取图片的锚点
                        XSSFClientAnchor anchor = (XSSFClientAnchor) picture.getAnchor();

                        Cell cell = sheet.getRow(4).getCell(1);
                        // 获取零件编码
                        String partCode = sheet.getRow(3).getCell(3).getStringCellValue();
                        // 获取零件名称
                        String partName = sheet.getRow(3).getCell(5).getStringCellValue();
                        if ("图示：".equals(cell.getStringCellValue()) && cell.getRowIndex() > 1) {
                            // 生成带有中文行列信息的文件名
                            String imageFileName = partCode + "_" + partName + (j+1) + ".jpg";
                            // 临时文件路径
                            String path = imagePath + File.separator + imageFileName;
                            FileOutputStream fos =
                                    new FileOutputStream(path);
                            fos.write(data);
                            fos.flush();
                            pathList.add(path);
                            fos.close();
                        }
                        j++;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pathList;
    }


    /**
     * 自定义方法获取文件的 MIME 类型，可以通过扩展名来判断
     * @param fileName
     * @return
     */
    private static String getContentType(String fileName) {
        if (fileName.endsWith(".jpg") || fileName.endsWith(".jpeg")) {
            return "image/jpeg";
        } else if (fileName.endsWith(".png")) {
            return "image/png";
        } else if (fileName.endsWith(".txt")) {
            return "text/plain";
        } else if (fileName.endsWith(".pdf")) {
            return "application/pdf";
        }
        // 默认返回应用程序/octet-stream
        return "application/octet-stream";
    }

    private static List<List<Object>> removeEmptyArrays(List<List<Object>> lists) {
        lists.removeIf(List::isEmpty);
        return lists;
    }

}
